#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2015年7月13日

@author: chenyongbing
'''
import sys,os
#current_dir = os.path.dirname(__file__)
current_dir = os.path.split(os.path.realpath(__file__))[0]



sys.path.append('../')

from base.PyMySQL import ConnectMySQL

import re,datetime

def showTables():
    tabs = []
    query = 'show tables;'
    #print query
    ret = myConnectMySQL.SelectAll(query)
    for tab_list in ret:
        tab = tab_list[0]
        tabs.append(tab)
    return  tabs


def make_select_query_by_group(tabs,group,st,et):
    for tab in tabs:
        if tab in ('mac_customer','mac_employee','mac_walkby'):continue
        
        ret = myConnectMySQL.descTab(tab)
        #print ret  
        #print '--table : %s --'%tab
        if 'dgroup' in ret:
            if 'day' in ret:
                query = 'delete from %s where dgroup=%s and day between "%s" and "%s";'%(tab,group,st,et)
            elif 'last_time' in ret:
                query = 'delete from %s where dgroup=%s and last_time between "%s" and "%s";'%(tab,group,st,et)
            print query
        if 'any_group' in ret:
            if 'day' in ret:
                query = 'delete from %s where any_group=%s and day between "%s" and "%s";'%(tab,group,st,et)
            elif 'last_time' in ret:
                query = 'delete from %s where any_group=%s and last_time between "%s" and "%s";'%(tab,group,st,et)
            print query


def change_group(tabs,copy_group_rules={},st=None,et=None,type='insert'):
    datas = []
    
    for ngroup , groupInfo in copy_group_rules.iteritems():
        group_type = groupInfo['group_type']
        city = groupInfo['city']
        prov = groupInfo['prov']
        comp = groupInfo['comp']
        ogroup = groupInfo['copy_from']
        
        for tab in tabs:
            if tab  in ('mac_customer','mac_employee','mac_walkby'):continue
            
            if not re.search('hour|day|week|month|quarter|stats',tab):continue
            
            ret = myConnectMySQL.descTab(tab)
            if 'id' in ret:ret.remove('id')
            fields = ','.join(ret)
            if 'dgroup' in ret:
                groupField = 'dgroup'
                nfields = re.sub('dgroup',str(ngroup),fields)
            elif 'any_group' in ret :
                groupField = 'any_group'
                nfields = re.sub('any_group',str(ngroup),fields)
            elif 'anyGroup' in ret:
                groupField = 'anyGroup'
                nfields = re.sub('anyGroup',str(ngroup),fields)
            else:
                continue
                nfields = fields
            
            if 'city_group' in ret:
                nfields = re.sub('city_group',str(city),nfields)
                
            if 'prov_group' in ret:
                nfields = re.sub('prov_group',str(prov),nfields)
            
            if 'comp_group' in ret:
                nfields = re.sub('comp_group',str(comp),nfields)
                
            if 'group_type' in ret:
                nfields = re.sub('group_type',str(group_type),nfields)
            
            if str(group_type) == '1' and tab == 'city_mac_customer_stats':
                ntab = 'prov_mac_customer_stats'
            elif str(group_type) == '0' and tab == 'city_mac_customer_stats':
                ntab = 'comp_mac_customer_stats'
            else:
                ntab = tab
            if 'day' in ret:
                dayField = 'day'
            if 'last_time' in ret:
                dayField = 'last_time'
            if type=='update':
                if st and et:
                    query = 'update %s set %s="%s" where %s="%s" and %s between "%s" and "%s";'%(tab,groupField,ngroup,groupField,ogroup,dayField,st,et)
                else:
                    query = 'update %s set %s="%s" where %s="%s"'%(tab,groupField,ngroup,groupField,ogroup)
            elif type=='delete':
                if st and et:
                    query = 'delete from %s where %s=%s and %s between "%s" and "%s";'%(tab,groupField,ogroup,dayField,st,et)
                else:
                    query = 'delete from %s where %s=%s;'%(tab,groupField,ngroup)
            elif type=='copy':
                
                query = 'insert into %s( '%ntab
                query = query + fields 
                query = query + ') select '+nfields + ' from %s where %s="%s"'%(tab,groupField,ogroup)
                if st and et:
                    if st!=et:
                        query += ' and %s between "%s" and "%s"'%(dayField,st,et)
                    else:
                        query += ' and %s="%s"'%(dayField,st)
                query +=';'
            datas.append( query )
    return datas

if __name__=='__main__':
    import argparse
    parser = argparse.ArgumentParser(description='args')
    parser.add_argument('--run',metavar="copy",default='copy')
    parser.add_argument('--db_config',metavar="zhimind host",default='zm09:3306:zhimind')
    parser.add_argument('--day',metavar="day",default=None)
    
    
    args = parser.parse_args()
    db_config = args.db_config
    day = args.day
    runType = args.run
    
#     copy_group_rules = {32024125:{'name':'门店A','group_type':4,'copy_from':'32014416','city':'32024123','prov':'32024122','comp':'32024121'},
#                        32024127:{'name':'门店B','group_type':4,'copy_from':'32014490','city':'32024123','prov':'32024122','comp':'32024121'},
#                        32024123:{'name':'北京-市级','group_type':2,'copy_from':'32014414','city':'32024123','prov':'32024122','comp':'32024121'},
#                        32024122:{'name':'北京-省级','group_type':1,'copy_from':'32014414','city':'-1','prov':'32024122','comp':'32024121'},
#                        32024121:{'name':'公司','group_type':0,'copy_from':'32014414','city':'-1','prov':'-1','comp':'32024121'},
#                        }
    copy_group_rules = {32015571:{'name':'门店A','group_type':4,'copy_from':'32014416','city':'32015569','prov':'32015568','comp':'32015567'},
                       32015573:{'name':'门店B','group_type':4,'copy_from':'32014490','city':'32015569','prov':'32015568','comp':'32015567'},
                       32015569:{'name':'北京-市级','group_type':2,'copy_from':'32014414','city':'32015569','prov':'32015568','comp':'32015567'},
                       32015568:{'name':'北京-省级','group_type':1,'copy_from':'32014414','city':'-1','prov':'32015568','comp':'32015567'},
                       32015567:{'name':'公司','group_type':0,'copy_from':'32014414','city':'-1','prov':'-1','comp':'32015567'},
                       }
    #32015571,32015573,32015569,32015568,32015567
    #db_config = 'localhost:3306:zhimind'
    host,port,db = db_config.split(':')
    
    myConnectMySQL = ConnectMySQL(host=host,user='root',password='zhimatech',db=db)
    
    tabs = showTables()
    
    cmdList = change_group(tabs,copy_group_rules=copy_group_rules,type='copy')
    today = datetime.date.today().strftime('%Y-%m-%d')
    save_file = 'log/%s.%s.sql'%(runType,today)
    
    fr = open(save_file,'w')
    fr.write('\n'.join(cmdList))
    fr.close()
    for line in cmdList:
        print line
